import math
import re

import xlrd
from model.UniversityPlanScore import UniversityPlanScore
from model.UniversityScoreRank import UniversityScoreRank
from model.UniversityMajorRank import UniversityMajorRank
from model.UniversityPlanScoreError import UniversityPlanScoreError
from model.University import University
from model.OneScoreRank import OneScoreRank
import sys


# 导入《本科第二批预科A类》数据 理科
def import_score_1():
    data = xlrd.open_workbook('../static/excel_2023/AA.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    universityPlanScore = UniversityPlanScore()
    university = University()
    oneScoreRank = OneScoreRank()
    universityScoreRank = UniversityScoreRank()
    universityMajorRank = UniversityMajorRank()
    for i in range(rowNum):
        print(i)
        row = dict()
        university_row = dict()
        if i > 0:
            for j in range(colNum):
                row['batch'] = 13
                if j == 0:
                    row['year'] = int(table.cell(i, j).value)
                if j == 1:
                    row['code'] = int(table.cell(i, j).value)
                    university_row = university.getOneByCode(row['code'])
                if j == 2:
                    row['name'] = table.cell(i, j).value
                if university_row:
                    row['f211'] = university_row['f211']
                    row['f985'] = university_row['f985']
                    row['dual_class_name'] = university_row['dual_class_name']
                    row['nature_name'] = university_row['nature_name']
                    row['province_code'] = university_row['province_code']
                    row['city_code'] = university_row['city_code']
                    row['province'] = university_row['province']
                    row['city'] = university_row['city']
                else:
                    row['f211'] = 0
                    row['f985'] = 0
                    row['dual_class_name'] = 0
                    row['nature_name'] = 0
                    row['province_code'] = 0
                    row['city_code'] = 0
                    row['province'] = ''
                    row['city'] = ''

                if j == 3:
                    if table.cell(i, j).value:
                        row_1 = row
                        row_1['type'] = 1
                        row_1['lowest_score'] = int(table.cell(i, j).value)

                        # 计算最低分，最低排名
                        lowest_rank = oneScoreRank.getOneByScore(row['lowest_score'], row['year'], row['type'])
                        if lowest_rank:
                            row_1['lowest_rank'] = lowest_rank['total_num']
                        else:
                            row_1['lowest_rank'] = 0

                        row_1['highest_score'] = 0
                        row_1['highest_rank'] = 0
                        row_1['mean_score'] = 0
                        row_1['mean_rank'] = 0
                        row_1['plan_number'] = 0
                        row_1['submit_number'] = 0
                        row_1['enter_number'] = 0
                        row_1['score'] = ''
                        print(row_1)
                        universityScoreRank.toInsert(row_1)
                        row_1_1 = row_1
                        row_1_1['uname'] = row['name']
                        row_1_1['name'] = '少数民族预科班'
                        del row_1_1['highest_score']
                        del row_1_1['highest_rank']
                        print(row_1_1)
                        universityMajorRank.toInsert(row_1_1)


# 导入《本科第二批预科A类》数据 文科
def import_score_2():
    data = xlrd.open_workbook('../static/excel_2023/AA.xls')
    table = data.sheet_by_name('Sheet1')
    rowNum = table.nrows
    colNum = table.ncols
    print(rowNum, colNum)
    universityPlanScore = UniversityPlanScore()
    university = University()
    oneScoreRank = OneScoreRank()
    universityScoreRank = UniversityScoreRank()
    universityMajorRank = UniversityMajorRank()
    for i in range(rowNum):
        print(i)
        row = dict()
        university_row = dict()
        if i > 0:
            for j in range(colNum):
                row['batch'] = 13
                if j == 0:
                    row['year'] = int(table.cell(i, j).value)
                if j == 1:
                    row['code'] = int(table.cell(i, j).value)
                    university_row = university.getOneByCode(row['code'])
                if j == 2:
                    row['name'] = table.cell(i, j).value
                if university_row:
                    row['f211'] = university_row['f211']
                    row['f985'] = university_row['f985']
                    row['dual_class_name'] = university_row['dual_class_name']
                    row['nature_name'] = university_row['nature_name']
                    row['province_code'] = university_row['province_code']
                    row['city_code'] = university_row['city_code']
                    row['province'] = university_row['province']
                    row['city'] = university_row['city']
                else:
                    row['f211'] = 0
                    row['f985'] = 0
                    row['dual_class_name'] = 0
                    row['nature_name'] = 0
                    row['province_code'] = 0
                    row['city_code'] = 0
                    row['province'] = ''
                    row['city'] = ''

                if j == 4:
                    if table.cell(i, j).value:
                        row_2 = row
                        row_2['type'] = 2
                        row_2['lowest_score'] = int(table.cell(i, j).value)

                        # 计算最低分，最低排名
                        lowest_rank = oneScoreRank.getOneByScore(row['lowest_score'], row['year'], row['type'])
                        if lowest_rank:
                            row_2['lowest_rank'] = lowest_rank['total_num']
                        else:
                            row_2['lowest_rank'] = 0

                        row_2['highest_score'] = 0
                        row_2['highest_rank'] = 0
                        row_2['mean_score'] = 0
                        row_2['mean_rank'] = 0
                        row_2['plan_number'] = 0
                        row_2['submit_number'] = 0
                        row_2['enter_number'] = 0
                        row_2['score'] = ''
                        universityScoreRank.toInsert(row_2)
                        print(row_2)

                        row_2_2 = row_2
                        row_2_2['uname'] = row['name']
                        row_2_2['name'] = '少数民族预科班'
                        del row_2_2['highest_score']
                        del row_2_2['highest_rank']
                        print(row_2_2)
                        universityMajorRank.toInsert(row_2_2)


if __name__ == "__main__":
    import_score_2()